﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Helper;
using CallCenter.Data.Models;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;


namespace CallCenter.Data.BizUtilities
{
    public class TaskUtility : BaseUtility
    {
        public TaskUtility(DataManager manager)
            : base(manager)
        { }



        public ReturnMessage<TaskViewModel> InsertToTask(TaskViewModel model)
        {
            var returnMessage = new ReturnMessage<TaskViewModel>();
            //need to ceck whether admin or not
         

            if (String.IsNullOrEmpty(model.Notes))
                model.Notes = "";
            if (String.IsNullOrEmpty(model.description))
                model.description = "";
            if (model.startDate.HasValue)
            {
                // Check if start day is on weekend, update to next coming Monday if so
                if (model.startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                    model.startDate = model.startDate.Value.AddDays(2);
                else if (model.startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                    model.startDate = model.startDate.Value.AddDays(1);
            }

            #region check if there are any dependent task
            int dependTaskId = 0;
            if (model.DependTaskId != 0)
                dependTaskId = model.DependTaskId;

            DataRow taskDr = this.Bridge.TaskUtility.GetDependTaskRow(dependTaskId);
            if (taskDr != null)
            {
               
                    model.startDate = DateTime.Parse(taskDr["endDate"].ToString());
                    //if startDate == Friday
                    model.startDate = AddBusinessDays(model.startDate, (Convert.ToInt16(taskDr["gap"].ToString()) + 1));
                    if (model.startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                        model.startDate = model.startDate.Value.AddDays(2);
                    else if (model.startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                        model.startDate = model.startDate.Value.AddDays(1);
                
            }
            #endregion
            #region get only weekdays and add it to duration
            if (model.startDate.HasValue)
            {
                DateTime TempendDate = model.startDate.Value.AddDays(model.NoOfDays);
            }
                Nullable<DateTime> endDate;
            
          
                if (model.NoOfDays != 0)
                {
                    endDate = AddBusinessDays(model.startDate, model.NoOfDays);
                }
                else
                    endDate = model.startDate;
            
            

            #endregion
            int id = 0;
            if (model.taskName != "")
            {
                if (model.heldContactId != 0)
                {
                    id = this.Insert(model.productReqestId, model.taskName, model.description, model.startDate, endDate, model.modifiedDate, model.statusId, model.NoOfDays, model.DependTaskId, model.Gap, model.heldContactId, model.Budget, model.Notes);
                    returnMessage.Message = "Record has been inserted";
                }
                else
                {
                    returnMessage.Message = "'Record can not be inserted, Held By cannot be empty";
                }
            }
            else
            {
                returnMessage.Message = "'Record can not be inserted, Task name is required";
            }



            if (id > 0)
            {
                model.id = id;
                if (dependTaskId != 0)
                    this.InsertTaskDependence(id, model.DependTaskId, model.Gap);
                returnMessage.Status = true;
            }
            return returnMessage;
        }

        public ReturnMessage<TaskViewModel> UpdateToTask(TaskViewModel model)
        {
            var returnMessage = new ReturnMessage<TaskViewModel>();
            //  if(this.ValidateScratchPadModel(model))
            //   {

            if (String.IsNullOrEmpty(model.Notes))
                model.Notes = "";
            if (String.IsNullOrEmpty(model.description))
                model.description = "";
            if (model.startDate.HasValue)
            {
                // Check if start day is on weekend, update to next coming Monday if so
                if (model.startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                    model.startDate = model.startDate.Value.AddDays(2);
                else if (model.startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                    model.startDate = model.startDate.Value.AddDays(1);
            }
            bool completeTaskSchedule = this.Bridge.ProductRequestUtility.GetTaskScheduleComplete(model.productReqestId);

            #region get only weekdays and add it to duration

            Nullable<DateTime> endDate;
            if (model.NoOfDays != 0)
            {
                endDate = AddBusinessDays(model.startDate, model.NoOfDays);
            }
            else
                endDate = model.startDate.Value;

            #endregion

            #region check if there are any dependent task
            int dependTaskId = 0;
            if (model.DependTaskId != 0)
                dependTaskId = model.DependTaskId;

            DataRow taskDr = this.Bridge.TaskUtility.GetDependTaskRow(dependTaskId);
            if (taskDr != null)
            {
                if (model.startDate.HasValue)
                {
                    model.startDate = DateTime.Parse(taskDr["endDate"].ToString());
                    //if startDate == Friday

                    model.startDate = AddBusinessDays(model.startDate, (Convert.ToInt16(taskDr["gap"].ToString()) + 1));
                    if (model.startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                        model.startDate = model.startDate.Value.AddDays(2);
                    else if (model.startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                        model.startDate = model.startDate.Value.AddDays(1);

                    if (model.NoOfDays != 0)
                    {
                        endDate = AddBusinessDays(model.startDate, model.NoOfDays);
                    }
                    else
                        model.endDate = model.startDate;
                }
                    updateDependedTasks(model.id, model.startDate, endDate);
                
            }
            #endregion


            DataTable taskDt = this.GetAllDependTaskbyId(model.id);
            DataRow taskDrActual = this.GetDependTaskRow(model.id);

            int id = 0;
            //if (model.modifiedDate.HasValue)
            //{

            //}
            //else
            //{

            if(!model.modifiedDate.HasValue)
            {
                              
                    if (model.taskName != "")
                    {
                        if (model.heldContactId != 0)
                        {
                            id = this.Update(model.id, model.productReqestId, model.taskName, model.description, model.startDate, endDate, model.modifiedDate, model.statusId, model.NoOfDays, model.DependTaskId, model.Gap, model.heldContactId, model.Budget, model.Notes);
                            updateDependedSubTasks(model.id, model.startDate, endDate);
                        }
                        else
                            returnMessage.Message = "Date cannot be updated! Held by cannot be empty";
                    }
                    else

                        returnMessage.Message = "Date cannot be updated! Name cannot be empty";
                }

           
     

            
            else
            {
                if (model.taskName != "")
                {
                    if (model.heldContactId != 0)
                    {
                        id = this.UpdateModifiedDate(model.id, model.productReqestId, model.taskName, model.description, model.modifiedDate, endDate, model.statusId, model.NoOfDays, model.DependTaskId, model.Gap, model.heldContactId, model.Budget, model.Notes);
                        returnMessage.Message = "Date has been updated";

                        updateDependedSubTasks(model.id, model.startDate, endDate);
                    }
                    else
                        returnMessage.Message = "Date has been updated ! Held by cannot be empty";
                }
                else
                    returnMessage.Message = "Date has been updated ! Name by cannot be empty";
            }


            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                this.InsertTaskDependenceHistory(model.id, taskDrActual["taskName"].ToString(), taskDrActual["description"].ToString(), DateTime.Parse(taskDrActual["startDate"].ToString()), DateTime.Parse(taskDrActual["endDate"].ToString()), DateTime.Now, Convert.ToInt16(taskDrActual["heldContactid"].ToString()), taskDrActual["notes"].ToString(), model.statusId);
                endDate = AddBusinessDays(model.endDate, model.Gap + 1);

                //new start date for dependable task
                // Set end date according to task's gap
                endDate = AddBusinessDays(endDate, model.Gap + 1);
                this.updateDependedTasks(model.id, model.startDate, endDate);
                returnMessage.Status = true;
            }
            //if (completeTaskSchedule == true)
            //{
            //    this.sendMail(productRequestId);
            //}
            return returnMessage;
        }




        public int UpdateModifiedDate(int id, int productRequestId, string name, string desc, Nullable<DateTime> modifiedDate, Nullable<DateTime> endDate, int statusId,
                      int numOfDays, int depenTaskid, int gap, int heldByContactId, decimal budget, string notes)
        {
            int nRtn = -1;

            string sql = "UPDATE Tasks SET productReqestId=@productRequestId,taskName=@taskName, description=@description," +
                                     "modifiedDate=@startDate,endDate=@endDate,noOfDays=@noOfDays,statusId=@statusId," +
                                "DependTaskId=@DependTaskId,gap=@gap,heldContactId=@heldContactId,Budget=@Budget,notes=@notes" +
                                " WHERE id = @ID";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@productRequestId", SqlDbType.Int, 10, productRequestId);
                this.Factory.AddParameter(cmd, "@taskName", SqlDbType.NVarChar, 10, name);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 10, desc);
                this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 100, modifiedDate);
                this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);
                this.Factory.AddParameter(cmd, "@noOfDays", SqlDbType.Int, 100, numOfDays);
                this.Factory.AddParameter(cmd, "@DependTaskId", SqlDbType.Int, 10, depenTaskid);
                this.Factory.AddParameter(cmd, "@gap", SqlDbType.Int, 10, gap);
                this.Factory.AddParameter(cmd, "@heldContactId", SqlDbType.Int, 10, heldByContactId);
                this.Factory.AddParameter(cmd, "@Budget", SqlDbType.Int, 10, budget);
                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 100, notes);

                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //
        }

        public List<TaskViewModel> GetAllGrid(int productRequestId)
        {
            string sql = " SELECT t.*, cc.lastname +' '+ cc.firstname as heldContactName,s.description as statusName, " +
                                     " isnull((SELECT taskName FROM Tasks WHERE id = t.DependTaskId),'None') AS DependtaskName FROM Tasks t " +
                                     " INNER JOIN ddlProductRequests pr ON pr.id = t.productReqestId " +
                                     " INNER JOIN ccContacts cc ON cc.id = t.heldContactId  " +
                                     " INNER JOIN ddlStatus s ON s.id = t.statusId " +
                                     " WHERE t.productReqestId = " + productRequestId;
            return this.Factory.ExecuteObjects<TaskViewModel>(sql);


        }


        public List<DependTaskViewModel> GetAllDependTasks()
        {
            string sql = "SELECT t.taskName DependtaskName from tasks t";
            return this.Factory.ExecuteObjects<DependTaskViewModel>(sql);
        }

        public List<DependTaskViewModel> GetAllDependTasksByProductRequestExceptSelectedTask(int id, int productRequestId)
        {
            string sql = "";
            if (id == 0)
                sql = "SELECT t.taskName DependtaskName  from tasks t" +
                                        " WHERE t.productReqestId = " + productRequestId;
            else
                sql = "SELECT t.taskName DependtaskName from tasks t" +
                                        " WHERE t.productReqestId = " + productRequestId + " and t.id <> " + id +
                                        " and t.dependtaskid != " + id;

            return this.Factory.ExecuteObjects<DependTaskViewModel>(sql);
        }
        public int GetDependTaskIdByName(string name)
        {
            int result = 0;
            string sql = string.Format("select id from Tasks where taskName = '{0}'", name);
            try
            {
                result = Convert.ToInt32(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;

        }


        public DataTable GetOtherTask(int productRequestId)
        {
            string sql = "SELECT t.taskName, t.id FROM Tasks t " +
                                    "INNER JOIN ddlProductRequests pr ON pr.id = t.productReqestId " +
                                    " WHERE t.productReqestId = " + productRequestId;
            return this.Factory.ExecuteTable(sql);
        }

        public int Insert(int productRequestId, string name, string desc, Nullable<DateTime> startDate, Nullable<DateTime> endDate, Nullable<DateTime> modifiedDate, int statusId,
                             int numOfDays, int depenTaskid, int gap, int heldByContactId, decimal budget, string notes)
        {
            int nRtn = -1;

            string sql = "Insert INTO Tasks " +
                                "(productReqestId,taskName, description,startDate,endDate, modifiedDate,noOfDays," +
                                "DependTaskId,gap,heldContactId,Budget,statusId,notes) " +
                                "Values(@productRequestId,@taskName, @description,@startDate,@endDate,@modifiedDate, @noOfDays," +
                                "@DependTaskId,@gap,@heldContactId,@Budget,@statusId,@notes) SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@productRequestId", SqlDbType.Int, 10, productRequestId);
                this.Factory.AddParameter(cmd, "@taskName", SqlDbType.NVarChar, 10, name);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 10, desc);
                //this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 100, startDate);
                //this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);
                this.Factory.AddParameter(cmd, "@noOfDays", SqlDbType.Int, 100, numOfDays);
                this.Factory.AddParameter(cmd, "@DependTaskId", SqlDbType.Int, 10, depenTaskid);
                this.Factory.AddParameter(cmd, "@gap", SqlDbType.Int, 10, gap);
                this.Factory.AddParameter(cmd, "@heldContactId", SqlDbType.Int, 10, heldByContactId);
                this.Factory.AddParameter(cmd, "@Budget", SqlDbType.Int, 10, budget);
                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 100, notes);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);


                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);

                if ((modifiedDate == null) || (modifiedDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@modifiedDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@modifiedDate", SqlDbType.DateTime, 10, modifiedDate);

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }

        public int Update(int id, int productRequestId, string name, string desc, Nullable<DateTime> startDate, Nullable<DateTime> endDate, Nullable<DateTime> modifiedDate, int statusId,
                        int numOfDays, int depenTaskid, int gap, int heldByContactId, decimal budget, string notes)
        {
            int nRtn = -1;

            string sql = "UPDATE Tasks SET productReqestId=@productRequestId,taskName=@taskName, description=@description," +
                                    "startDate=@startDate,endDate=@endDate, modifiedDate=@modifiedDate,noOfDays=@noOfDays,statusId=@statusId," +
                               "DependTaskId=@DependTaskId,gap=@gap,heldContactId=@heldContactId,Budget=@Budget,notes=@notes" +
                               " WHERE id = @ID";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@productRequestId", SqlDbType.Int, 10, productRequestId);
                this.Factory.AddParameter(cmd, "@taskName", SqlDbType.NVarChar, 10, name);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 10, desc);
             
                this.Factory.AddParameter(cmd, "@noOfDays", SqlDbType.Int, 100, numOfDays);
                this.Factory.AddParameter(cmd, "@DependTaskId", SqlDbType.Int, 10, depenTaskid);
                this.Factory.AddParameter(cmd, "@gap", SqlDbType.Int, 10, gap);
                this.Factory.AddParameter(cmd, "@heldContactId", SqlDbType.Int, 10, heldByContactId);
                this.Factory.AddParameter(cmd, "@Budget", SqlDbType.Int, 10, budget);
                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 100, notes);

                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);

                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);

                if ((modifiedDate == null) || (modifiedDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@modifiedDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@modifiedDate", SqlDbType.DateTime, 10, modifiedDate);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //
        }


        public int Update(int id, Nullable<DateTime> startDate, Nullable<DateTime> endDate,
                        int numOfDays, int gap)
        {
            int nRtn = -1;
            string sql = "UPDATE Tasks SET startDate=@startDate,endDate=@endDate, noOfDays=@noOfDays," +
                                           "gap=@gap" +
                                           " WHERE id = @ID";


            using (var cmd = this.Factory.GetCommand(sql))
            {

                this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 100, startDate);
                this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);
                this.Factory.AddParameter(cmd, "@noOfDays", SqlDbType.Int, 100, numOfDays);
                this.Factory.AddParameter(cmd, "@gap", SqlDbType.Int, 10, gap);

                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //
        }



        public DataRow GetDependTaskRow(int taskId)
        {
            string sql = "SELECT * FROM Tasks t " +
                                        " WHERE t.id = " + taskId;

            return this.Factory.ExecuteRecord(sql);

            //if (dt.Rows.Count == 1)
            //    return dt.Rows[0];
            //else
            //    return null;
        }

        public static DateTime AddBusinessDays(Nullable<DateTime> startDate, int days)
        {
            if (days == 0) return startDate.Value;

            if (startDate.HasValue)
            {
                if (startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                {
                    startDate = startDate.Value.AddDays(2);
                    days -= 1;
                   
                }
                else if (startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                {
                    startDate = startDate.Value.AddDays(1);
                    days -= 1;
                  
                }


                startDate = startDate.Value.AddDays(days / 5 * 7);
                int extraDays = days % 5;

                if ((int)startDate.Value.DayOfWeek + extraDays > 5)
                {
                    extraDays += 2;
                }


                return startDate.Value.AddDays(extraDays);
            }
            return DateTime.Now;
            
            
        }


        #region task Dependence
        public int InsertTaskDependence(int taskId, int taskDependentId, int gap)
        {
            int nRtn = -1;

            string sql = "Insert INTO ccTaskDependence " +
                                  "(taskId,taskDependentId, gap) " +
                                  "Values(@taskId,@taskDependentId, @gap) SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@taskId", SqlDbType.Int, 10, taskId);
                this.Factory.AddParameter(cmd, "@taskDependentId", SqlDbType.Int, 10, taskDependentId);
                this.Factory.AddParameter(cmd, "@gap", SqlDbType.Int, 100, gap);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;


        }

        public int UpdateTaskDependence(int id, int taskId, int taskDependentId, int gap)
        {
            int nRtn = -1;

            string sql = "UPDATE ccTaskDependence SET " +
                               "taskId=@taskId,taskDependentId=@taskDependentId, gap=@gap WHERE id=@ID";



            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@taskId", SqlDbType.Int, 10, id);
                this.Factory.AddParameter(cmd, "@taskDependentId", SqlDbType.Int, 10, taskDependentId);
                this.Factory.AddParameter(cmd, "@gap", SqlDbType.Int, 100, gap);
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }



        public void DeleteTaskDependence(int id) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ccTaskDependence " +
                "WHERE id = @ID ";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }




        public void DeleteTask(int id) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM Tasks " +
                "WHERE id = @ID ";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }

        public DataTable GetAllTaskDependence()
        {
            string sql = "SELECT * FROM ccTaskDependence";

            return this.Factory.ExecuteTable(sql);

        }




        public DataTable GetAllTaskDependence(int taskId)
        {
            string sql = "SELECT * FROM ccTaskDependence WHERE taskId = " + taskId;
            return this.Factory.ExecuteTable(sql);



        }

        public List<TaskHistoryVm> GetAllTaskDependenceHistory(int taskId)
        {
            string sql = " SELECT TOP 5 th.*,cc.lastname+', '+cc.firstname as HeldName,s.description statusName FROM ccTaskHistory th" +
                                      " INNER JOIN ccContacts cc ON cc.id = th.heldBy " +
                                      " INNER JOIN ddlStatus s ON s.id = th.status " +
                                      " WHERE taskId = " + taskId + " ORDER BY dateChanged DESC";

            return this.Factory.ExecuteObjects<TaskHistoryVm>(sql);

        }
        #endregion

        public DataTable GetAllDependTaskbyId(int taskId)
        {
            string sql = "SELECT * FROM Tasks t " +
                                     " WHERE t.DependTaskId = " + taskId;

            return this.Factory.ExecuteTable(sql);
        }

        //public int GetDependTaskId(int taskId)
        //{
        //    string sql = "SELECT t.DependTaskId FROM Tasks t " +
        //                               " WHERE t.id = " + taskId;

        //    SqlDataAdapter da = new SqlDataAdapter();
        //    DataTable dt = new DataTable();
        //    da.SelectCommand = cmdSelect;
        //    da.Fill(dt);

        //    if (dt.Rows.Count == 1)
        //        return Convert.ToInt16(dt.Rows[0]["DependTaskId"].ToString());
        //    else
        //        return -1;
        //}
        protected void updateDependedTasks(int parentId, Nullable<DateTime> startDate, Nullable<DateTime> endDate)
        {
            DataTable dt = this.GetAllDependTaskbyId(parentId);

            if (dt.Rows.Count != 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int id = Convert.ToInt16(dt.Rows[i]["id"].ToString());
                    DateTime sDate = startDate.Value;
                    DateTime nDate = endDate.Value;
                    int noOfDays = Convert.ToInt32(dt.Rows[i]["NoOfDays"].ToString());
                    int gap = Convert.ToInt32(dt.Rows[i]["gap"].ToString());
                    #region get only weekdays and add it to duration
                    nDate = AddBusinessDays(sDate, noOfDays);

                    #endregion
                    this.Update(id, sDate, nDate, noOfDays, gap);
                    nDate = AddBusinessDays(nDate, gap + 1);
                    updateDependedTasks(id, sDate, nDate);
                    updateDependedSubTasks(id, sDate, nDate);       //update table with subtasks
                }
            }

        }

        protected void updateDependedSubTasks(int parentId, Nullable<DateTime> startDate, Nullable<DateTime> endDate)
        {

            DataTable dt = this.Bridge.ProjectSubTaskUtility.GetAllGrids(parentId);

            if (dt.Rows.Count != 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int id = Convert.ToInt16(dt.Rows[i]["id"].ToString());
                    DateTime sDate = startDate.Value;
                    DateTime nDate = endDate.Value;
                    int noOfDays = Convert.ToInt32(dt.Rows[i]["NoOfDays"].ToString());
                    int g = Convert.ToInt32(dt.Rows[i]["gap"].ToString());
                    #region get only weekdays and add it to duration
                    nDate = AddBusinessDays(sDate, noOfDays);

                    #endregion
                    if (nDate.CompareTo(endDate) < 0)
                        this.Bridge.ProjectSubTaskUtility.UpdateDate(id, sDate, nDate); //update only start date and the end date of the subtask
                    else
                        this.Bridge.ProjectSubTaskUtility.UpdateDate(id, sDate, endDate);

                }
            }

        }

        public int InsertTaskDependenceHistory(int taskId, string name, string desc, DateTime startDate, DateTime endDate,
                       DateTime changeDate, int heldByContactId, string notes, int statusId)
        {
            int nRtn = -1;

            string sql = "Insert INTO ccTaskHistory " +
                                  "(taskId,name, description,startDate,endDate," +
                                  "dateChanged,heldBy,notes,status) " +
                                  "Values(@taskId,@name, @description,@startDate,@endDate," +
                                  "@dateChanged,@heldBy,@notes,@status) SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@taskId", SqlDbType.Int, 10, taskId);
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 10, name);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 10, desc);
                this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 100, startDate);
                this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);
                this.Factory.AddParameter(cmd, "@dateChanged", SqlDbType.SmallDateTime, 100, changeDate);
                this.Factory.AddParameter(cmd, "@heldBy", SqlDbType.Int, 10, heldByContactId);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 100, notes);
                this.Factory.AddParameter(cmd, "@status", SqlDbType.Int, 10, statusId);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }


        public DataTable PdfTask(int projectIds)
        {
            string sql = "SELECT t.*, pr.name as Project, s.description as status, " +
                 " c.lastName+', '+c.firstName as heldByName from Tasks t " +
                 " INNER JOIN ddlProductRequests pr on pr.id = t.productReqestId " +
                 " INNER JOIN ccContacts c on c.id = t.heldContactId " +
                 " INNER JOIN ddlStatus s on s.id = t.statusId  " +
                 " WHERE t.productReqestId = " + projectIds;


            return this.Factory.ExecuteTable(sql);

        }

        public List<DependTaskViewModel> GetAllDependTasksByProductRequest(int id, int productReqestId)
        {
            string sql = "SELECT t.taskName DependtaskName from tasks t" +
                                       " WHERE t.productReqestId = " + productReqestId +
                                       " and t.dependtaskid = " + id;

            return this.Factory.ExecuteObjects<DependTaskViewModel>(sql);
        }
    }

}

